# -*-coding:utf-8 -*-
"""
沪深股通标的港股机构持股明细
-输出图表
为:
TradeDate  SECUCODE  SECURITYNAME  HKINS_1 .... HKINS_163  (机构名称对应的code_)对应的数据为 当日持股总量
2020-03-16 000006.SZ 深振业A        0（没有为0）  10400 （股）
"""
import pandas as pd
import numpy as np
import datetime
import common.sql as db
from sqlalchemy.types import NVARCHAR, VARCHAR, Integer, Float, Date
import matplotlib.pyplot as plt
# 解决字体问题
from pylab import mpl

# 指定默认字体：解决plot不能显示中文问题
mpl.rcParams['font.sans-serif'] = ['Microsoft YaHei']
# 解决保存图像是负号'-'显示为方块的问题
mpl.rcParams['axes.unicode_minus'] = False

HK_INS_TABLE_ = "hk_institutions_name"

HK_INS_TABLE_SQL_ = "SELECT index_,code_,name_ from {0}".format(HK_INS_TABLE_)
# 从数据库获取数据，->dataFrame
hk_ins_df = db.getdata_from_sql(HK_INS_TABLE_SQL_)

# 1.得到最终表的columns
columns = hk_ins_df.iloc[0:, 1:2]
# 转化为list
col = np.array(columns).tolist()
cols = [x[0] for x in col]
cols.insert(0, "SECURITYNAME")
cols.insert(0, "SECUCODE")
cols.insert(0, "TradeDate")


new_table_prefix = "hk_ins_hold_detail"
tab_ = '300607sz'

hk_ins_hold_detail_sql = "SELECT * FROM `{0}` ;".format(
    new_table_prefix+tab_)
# 2.获取 需要展示的数据源
hk_ins_hold_detail = db.getdata_from_sql_query(
    hk_ins_hold_detail_sql, columns=None)

hk_ins_hold_detail['TradeDate'] = pd.to_datetime(
    hk_ins_hold_detail['TradeDate'])  # 将日期列转换成 时间格式 datetime64[ns]
# hk_ins_hold_detail.index=hk_ins_hold_detail['TradeDate']
hk_ins_hold_detail.sort_values(
    'TradeDate', inplace=True, ascending=True)  # 按照日期排序
hk_ins_hold_detail = hk_ins_hold_detail.reset_index(drop=True)  # 重置索引

for i in cols:
    if i not in ['TradeDate', 'SECUCODE', 'SECURITYNAME']:
        if hk_ins_hold_detail[i].sum() == 0:
            hk_ins_hold_detail.drop(i, axis=1, inplace=True)


hk_ins_hold_detail.index = hk_ins_hold_detail['TradeDate']
tem = hk_ins_hold_detail.drop(hk_ins_hold_detail.columns[0:3], axis=1)
rename_dict = {}
for i in tem.columns:
    df_ = hk_ins_df.query("code_=='{0}'".format(i))
    v = df_.name_.values[0]
    rename_dict[i] = v

tem.rename(columns=rename_dict, inplace=True) #重新修改columns

# print(tem.head())

ax = tem.plot(kind="line", figsize=(150, 150), stacked=True,
              fontsize=16, grid=True, table=True, style="-o", xticks=tem.index)
fig = ax.get_figure()
fig.savefig('./fig_line4.jpg')
# print(tem.head())

# hk_ins_hold_detail.to_csv("./tsz.csv")
# print(hk_ins_hold_detail.tail(10))
# print(len(hk_ins_hold_detail.columns))
"""
date_x = hk_ins_hold_detail.TradeDate.values
data_1 = hk_ins_hold_detail.HKINS_5.values
# print(data_1)
fig, ax = plt.subplots(figsize=(100, 50), linewidth=5, edgecolor='.5')
ax.plot(date_x, data_1, linestyle='-',
        linewidth=3, color='.2', label='HKINS_5')
text_kwargs = dict(fontsize=20, family='宋体')
ax.set_title("拓斯达", **text_kwargs)
ax.set_ylabel("ShareHolding 股", **text_kwargs)
# fig.legend()
plt.show()
"""

"""
x = [-3, -5, 7]
y = [10, 2, 5]
plt.figure(figsize=(15, 3))
plt.plot(x, y)
plt.xlim(0, 10)
plt.ylim(-3, 8)
plt.xlabel('X Axis')
plt.ylabel('Y Axis')
plt.title('Line Plot')
plt.suptitle('Figure Title', size=20, y=1.03)
plt.legend()
plt.show()
"""

# print(hk_ins_hold_detail.dtypes)
# 4.处理数据
